package cn.dao;

import cn.comon.CSVUtil;
import cn.comon.DBConnection;
import cn.entity.Client;
import cn.entity.Goods;

import javax.swing.*;
import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GoodsDao implements AutoCloseable {

    private Connection conn;

    public GoodsDao() {
        // 获取数据库连接
        conn = new DBConnection().getConn();
    }

    public List<Object[]> selectAll() {
        List<Object[]> data = new ArrayList<>();
        String sql = "SELECT * FROM goods"; // 假设商品表名为'goods'
        try (Statement sta = conn.createStatement();
             ResultSet rs = sta.executeQuery(sql)) {
            while (rs.next()) {
                Object[] rowData = new Object[6]; // 假定商品有6个字段
                rowData[0] = rs.getString(1);
                rowData[1] = rs.getString(2);
                rowData[2] = rs.getFloat(3); // 假设价格是浮点数类型
                rowData[3] = rs.getString(4);
                rowData[4] = rs.getString(5);
                rowData[5] = rs.getString(6);
                data.add(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return data;
    }


    public void addGoods(List<Goods> ls) {
        String checkSql = "SELECT COUNT(*) FROM Client WHERE cid = ?";
        String insertSql = "INSERT INTO Goods (gid, gname, price, cid, introduction, notes) VALUES (?, ?, ?, ?, ?, ?)";

        try (PreparedStatement checkStmt = conn.prepareStatement(checkSql);
             PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {

            conn.setAutoCommit(false);

            for (Goods goods : ls) {
                // 检查cid是否存在
                checkStmt.setString(1, goods.getCid());
                ResultSet rs = checkStmt.executeQuery();
                rs.next();
                int count = rs.getInt(1);

                if (count == 0) { // cid不存在于Client表中
                    JOptionPane.showMessageDialog(null, "客户编号(" + goods.getCid() + ")不存在，请检查后重试！", "错误", JOptionPane.ERROR_MESSAGE);
                    throw new SQLException("CID not found in Client table."); // 抛出异常以便于后续处理或回滚
                }

                // cid存在，继续插入操作
                insertStmt.setString(1, goods.getGid());
                insertStmt.setString(2, goods.getGname());
                insertStmt.setFloat(3, goods.getPrice());
                insertStmt.setString(4, goods.getCid());
                insertStmt.setString(5, goods.getIntroduction());
                insertStmt.setString(6, goods.getNotes());
                insertStmt.addBatch();
            }

            insertStmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            JOptionPane.showMessageDialog(null, "插入商品时发生错误: " + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
            e.printStackTrace();
        }
    }

    public void deleteGood(String gid) {
        String sql = "DELETE FROM goods WHERE gid = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, gid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateGoods(Goods good, String originalGid) {
        String sql = "UPDATE goods SET gid = ?, gname = ?, price = ?, cid = ?, introduction = ?, notes = ? WHERE gid = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, good.getGid());
            ps.setString(2, good.getGname());
            ps.setFloat(3, good.getPrice());
            ps.setString(4, good.getCid());
            ps.setString(5, good.getIntroduction());
            ps.setString(6, good.getNotes());
            ps.setString(7, originalGid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<Goods> queryGoods(Goods criteria) {
        List<Goods> goodsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM Goods WHERE 1=1");
        List<Object> params = new ArrayList<>(); // 使用列表收集所有参数
        // 构建SQL和参数列表
        if (criteria.getGid() != null && !criteria.getGid().isEmpty()) {
            sql.append(" AND Gid = ?");
            params.add(criteria.getGid());
        }
        if (criteria.getGname() != null && !criteria.getGname().isEmpty()) {
            sql.append(" AND GName = ?");
            params.add(criteria.getGname());
        }

        if (criteria.getPrice() == 0.0 && criteria.getGid() == null && criteria.getGname() == null && criteria.getCid() == null && criteria.getIntroduction() == null && criteria.getNotes() == null) {
            sql.append(" AND Price = ?");
            params.add(criteria.getPrice());
        }
        if (criteria.getPrice() != 0.0) {
            sql.append(" AND Price = ?");
            params.add(criteria.getPrice());
        }
        if (criteria.getCid() != null && !criteria.getCid().isEmpty()) {
            sql.append(" AND Cid = ?");
            params.add(criteria.getCid());
        }
        if (criteria.getIntroduction() != null && !criteria.getIntroduction().isEmpty()) {
            sql.append(" AND Introduction = ?");
            params.add(criteria.getIntroduction());
        }
        if (criteria.getNotes() != null && !criteria.getNotes().isEmpty()) {
            sql.append(" AND Notes = ?");
            params.add(criteria.getNotes());
        }

        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            // 设置参数值
            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i);
                if (param instanceof String) {
                    ps.setString(i + 1, (String) param);
                } else if (param instanceof Float) {
                    ps.setFloat(i + 1, (Float) param);
                }
                // 可以根据需要添加更多类型的处理
            }

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Goods goods = new Goods();
                    goods.setGid(rs.getString("Gid"));
                    goods.setGname(rs.getString("GName"));
                    goods.setPrice(rs.getFloat("Price"));
                    goods.setCid(rs.getString("Cid"));
                    goods.setIntroduction(rs.getString("Introduction"));
                    goods.setNotes(rs.getString("Notes"));
                    goodsList.add(goods);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsList;
    }

    private void exportClientsToCSV(List<Client> clients) {
        String[] headers = {"客户编号", "客户名称", "客户简称", "地址", "公司电话", "邮件", "联系人", "联系人电话", "备注"};
        List<Object[]> data = new ArrayList<>();

        for (Client client : clients) {
            data.add(new Object[]{
                    client.getCid(),
                    client.getCName(),
                    client.getShortName(),
                    client.getAddress(),
                    client.getCompanyPhone(),
                    client.getEmail(),
                    client.getContact(),
                    client.getContactPhone(),
                    client.getNotes()
            });
        }

        File csvFile = CSVUtil.createCSVFile(headers, data, "output/path", "客户数据");
        if (csvFile != null) {
            JOptionPane.showMessageDialog(null, "客户数据已成功导出到: " + csvFile.getAbsolutePath());
        } else {
            JOptionPane.showMessageDialog(null, "导出客户数据失败");
        }
    }

    // 获取库存统计信息
    public List<Object[]> getStockStatistics() {
        List<Object[]> list = new ArrayList<>();
        String sql = "SELECT g.gid, g.gname, g.price, g.cid, SUM(pd.dcount) AS stock_quantity " +
                "FROM goods g JOIN PayDetail pd ON g.gid = pd.gid " +
                "GROUP BY g.gid, g.gname, g.price, g.cid";
        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Object[] row = new Object[5];
                row[0] = rs.getString("gid");
                row[1] = rs.getString("gname");
                row[2] = rs.getDouble("price");
                row[3] = rs.getString("cid");
                row[4] = rs.getInt("stock_quantity");
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    // 获取采购统计信息
    public List<Object[]> getPurchaseStatistics() {
        List<Object[]> list = new ArrayList<>();
        String sql = "SELECT g.gid, g.gname, g.price, g.cid, pd.dcount AS purchase_quantity, pd.goodstotal AS purchase_amount " +
                "FROM goods g JOIN PayDetail pd ON g.gid = pd.gid";
        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Object[] row = new Object[6];
                row[0] = rs.getString("gid");
                row[1] = rs.getString("gname");
                row[2] = rs.getDouble("price");
                row[3] = rs.getString("cid");
                row[4] = rs.getInt("purchase_quantity");
                row[5] = rs.getDouble("purchase_amount");
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    @Override
    public void close() {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}